package com.hexinfo.dmpro.common.utils;

/**
 * @author yemw
 * @date 2017/10/29
 */
public interface CommonConstants {

	/**
	 * header 中租户ID
	 */
	String TENANT_ID = "TENANT-ID";

	/**
	 * header 中版本信息
	 */
	String VERSION = "VERSION";

	/**
	 * 租户ID
	 */
	Integer TENANT_ID_1 = 1;

	/**
	 * 删除,否,非
	 */
	String STATUS_DEL = "1";

	/**
	 * 正常,是
	 */
	String STATUS_NORMAL = "0";

	/**
	 * 锁定
	 */
	String STATUS_LOCK = "9";

	/**
	 * 菜单树根节点
	 */
	Integer MENU_TREE_ROOT_ID = -1;

	/**
	 * 编码
	 */
	String UTF8 = "UTF-8";

	/**
	 * 前端工程名
	 */
	String FRONT_END_PROJECT = "dmpro-ui";

	/**
	 * 后端工程名
	 */
	String BACK_END_PROJECT = "dmpro";

	/**
	 * 公共参数
	 */
	String DMPRO_PUBLIC_PARAM_KEY = "DMPRO_PUBLIC_PARAM_KEY";

	/**
	 * 成功标记
	 */
	Integer SUCCESS = 0;

	/**
	 * 失败标记
	 */
	Integer FAIL = 1;

	/**
	 * 默认存储bucket
	 */
	String BUCKET_NAME = "dmpro";

	/**
	 * 滑块验证码
	 */
	String IMAGE_CODE_TYPE = "blockPuzzle";

	String HIVE_SQL_ORACLE_ROWNUM_COUNT =
					"SELECT \n" +
					"count(1)+10000 AS COUNT \n" +
					"FROM TBLS T \n" +
					"LEFT JOIN DBS D \n" +
					"ON T.DB_ID = D.DB_ID \n" +
					"LEFT JOIN SDS S \n" +
					"ON T.SD_ID = S.SD_ID \n" +
					"LEFT JOIN TABLE_PARAMS TP3 \n" +
					"ON T.TBL_ID = TP3.TBL_ID \n" +
					"AND TP3.PARAM_KEY = 'comment' \n" +
					"LEFT JOIN PARTITIONS P \n" +
					"ON T.TBL_ID = P.TBL_ID\n" +
					"WHERE \n"+
					"D.NAME IN ('stg', 'ods','dw')\n" +
					"AND D.NAME IS NOT NULL ";

	/**
	 * HIVE元数据查询SQL（ORACLE）分页
	 */
	String  HIVE_SQL_ORACLE_ROWNUM=
					"SELECT * FROM (\n" +
					"SELECT ROW_NUMBER() OVER(ORDER BY location DESC ,P.PART_NAME DESC) AS row_num ,\n" +
					"D.NAME tblDatabaseName , \n" +
					"T.TBL_NAME tblName , \n" +
					"S.LOCATION location , \n" +
					"P.PART_NAME partName , \n" +
					"'待确认' syncType \n" +
					"FROM TBLS T \n" +
					"LEFT JOIN DBS D \n" +
					"ON T.DB_ID = D.DB_ID \n" +
					"LEFT JOIN SDS S \n" +
					"ON T.SD_ID = S.SD_ID \n" +
					"LEFT JOIN PARTITIONS P \n" +
					"ON T.TBL_ID = P.TBL_ID\n" +
					"WHERE D.NAME IN ('stg', 'ods','dw') \n" +
					")  WHERE row_num BETWEEN ? AND ?";

	String HIVE_SQL_ORACLE_INCREMENT_COUNT =
					"SELECT \n" +
					"count(1) as COUNT \n" +
					"  from (\n" +
					"select D.NAME,\n" +
					"     T.TBL_NAME,\n" +
					"     S.LOCATION,\n" +
					"     P.PART_NAME \n" +
					"  from TBLS t\n" +
					" inner join TABLE_PARAMS tp3 \n" +
					"    on T.TBL_ID = TP3.TBL_ID \n" +
					"   and (tp3.PARAM_KEY  = 'transient_lastDdlTime' OR tp3.PARAM_KEY  = 'last_modified_time') \n" +
					"   and TO_NCHAR(tp3.PARAM_VALUE) BETWEEN  ? AND ?\n" +
					"  LEFT JOIN DBS D \n" +
					"    ON T.DB_ID = D.DB_ID \n" +
					"  LEFT JOIN SDS S \n" +
					"    ON T.SD_ID = S.SD_ID \n" +
					"  LEFT JOIN PARTITIONS P \n" +
					"    ON T.TBL_ID = P.TBL_ID  \n" +
					" WHERE D.NAME IN ('stg', 'ods','dw') \n" +
					" union  \n" +
					"select D.NAME,\n" +
					"     T.TBL_NAME,\n" +
					"     S.LOCATION,\n" +
					"     P.PART_NAME \n" +
					"  from TBLS t\n" +
					" inner join PARTITIONS p \n" +
					"    on t.TBL_ID = p.TBL_ID \n" +
					"   and TO_NCHAR(p.CREATE_TIME) BETWEEN ? AND ?\n" +
					"  LEFT JOIN DBS D \n" +
					"    ON T.DB_ID = D.DB_ID \n" +
					"  LEFT JOIN SDS S \n" +
					"    ON T.SD_ID = S.SD_ID \n" +
					" WHERE D.NAME IN ('stg', 'ods','dw') \n" +
					" ) t";

	/**
	 * HIVE元数据查询SQL（ORACLE）分页
	 * 上一次触发的时间戳
	 * 当前时间时间戳
	 * 分页
	 */
	String HIVE_SQL_ORACLE_INCREMENT=
			"SELECT * FROM (\n" +
			"select \n" +
			"ROW_NUMBER() OVER(ORDER BY location DESC,T.PART_NAME DESC) AS row_num ,\n" +
			"    T.NAME tblDatabaseName ,\n" +
			"    T.TBL_NAME tblName ,\n" +
			"    T.LOCATION location ,\n" +
			"    T.PART_NAME partName \n" +
			"  from (\n" +
			"select D.NAME,\n" +
			"     T.TBL_NAME,\n" +
			"     S.LOCATION,\n" +
			"     P.PART_NAME \n" +
			"  from TBLS t\n" +
			" inner join TABLE_PARAMS tp3 \n" +
			"    on T.TBL_ID = TP3.TBL_ID \n" +
			"   and (tp3.PARAM_KEY  = 'transient_lastDdlTime' OR tp3.PARAM_KEY  = 'last_modified_time') \n" +
			"   and TO_NCHAR(tp3.PARAM_VALUE) BETWEEN  ? AND ? \n" +
			"  LEFT JOIN DBS D \n" +
			"    ON T.DB_ID = D.DB_ID \n" +
			"  LEFT JOIN SDS S \n" +
			"    ON T.SD_ID = S.SD_ID \n" +
			"  LEFT JOIN PARTITIONS P \n" +
			"    ON T.TBL_ID = P.TBL_ID  \n" +
			" WHERE D.NAME IN ('stg', 'ods','dw') \n" +
			" union  \n" +
			"select D.NAME,\n" +
			"     T.TBL_NAME,\n" +
			"     S.LOCATION,\n" +
			"     P.PART_NAME \n" +
			"  from TBLS t\n" +
			" inner join PARTITIONS p \n" +
			"    on t.TBL_ID = p.TBL_ID \n" +
			"   and TO_NCHAR(p.CREATE_TIME) BETWEEN ? AND ? \n" +
			"  LEFT JOIN DBS D \n" +
			"    ON T.DB_ID = D.DB_ID \n" +
			"  LEFT JOIN SDS S \n" +
			"    ON T.SD_ID = S.SD_ID \n" +
			" WHERE D.NAME IN ('stg', 'ods','dw') \n" +
			" ) t" +
			")  WHERE row_num BETWEEN ? AND ?";

	/**
	 * 查询该表的全部分区字段
	 */
	String HIVE_SQL_ORACLE_PKEY_NAME =
					"SELECT \n" +
					"pk.PKEY_NAME pkeyName\n" +
					"FROM DBS d \n" +
					"INNER JOIN TBLS t \n" +
					"ON d.DB_ID = t.DB_ID  \n" +
					"and t.TBL_NAME = ? \n" +
					"LEFT JOIN PARTITIONS p \n" +
					"ON p.TBL_ID = t.TBL_ID \n" +
					"LEFT JOIN PARTITION_KEYS pk \n" +
					"ON p.TBL_ID = pk.TBL_ID \n" +
					"WHERE d.NAME = ? \n" +
					"GROUP BY pk.PKEY_NAME";

	/**
	 * 查询该表源的hdfs路径
	 */
	String HIVE_SQL_ORACLE_HDFS_LOCATION =
			"SELECT \n" +
			"DISTINCT \n" +
			"s.LOCATION \n" +
			"FROM DBS d \n" +
			"INNER JOIN TBLS t \n" +
			"ON d.DB_ID = t.DB_ID  \n" +
			"and t.TBL_NAME = ? \n" +
			"LEFT JOIN PARTITIONS p \n" +
			"ON p.TBL_ID = t.TBL_ID\n" +
			"LEFT JOIN SDS s \n" +
			"ON s.SD_ID = p.SD_ID\n" +
			"WHERE d.NAME = ? \n" +
			"${sql} ";

	/**
	 * 查询该UAT表的hdfs路径
	 */
	String HIVE_SQL_ORACLE_UAT_HDFS_LOCATION =
					"SELECT \n" +
					"DISTINCT \n" +
					"s.LOCATION\n" +
					"FROM TBLS t \n" +
					"INNER JOIN DBS d \n" +
					"ON d.DB_ID = t.DB_ID  \n" +
					"LEFT JOIN SDS s \n" +
					"ON s.SD_ID = t.SD_ID\n" +
					"WHERE d.NAME = ? \n" +
					"and t.TBL_NAME = ?";


	/**
	 * 查询Hive元数据中某库下某张表近两年数据的HDFS路径
	 */
	String HIVE_SQL_ORACLE_TWO_YEAR =
					"SELECT \n" +
					"(\n" +
					"case\n" +
					"   when  p.PART_NAME is null then s.LOCATION\n" +
					"else\n" +
					"   CONCAT(CONCAT(s.LOCATION,'/'), p.PART_NAME)\n" +
					"end \n" +
					") location\n" +
					"FROM DBS d \n" +
					"INNER JOIN TBLS t \n" +
					"ON d.DB_ID = t.DB_ID  \n" +
					"LEFT JOIN SDS s \n" +
					"ON s.SD_ID = t.SD_ID\n" +
					"LEFT JOIN PARTITIONS p \n" +
					"ON p.TBL_ID = t.TBL_ID \n" +
					"LEFT JOIN PARTITION_KEYS pk \n" +
					"ON p.TBL_ID = pk.TBL_ID \n" +
					"LEFT JOIN PARTITION_KEY_VALS pkv \n" +
					"ON pkv.PART_ID = p.PART_ID \n" +
					"AND pk.INTEGER_IDX = pkv.INTEGER_IDX \n" +
					"WHERE d.NAME = ? " +
					"AND t.TBL_NAME = ? \n" +
					"AND pk.PKEY_NAME = ? \n" +
					"${sql} \n" +
					"AND pkv.PART_KEY_VAL >= TO_CHAR(sysdate ${day},?)\n" +
					"ORDER BY pkv.PART_KEY_VAL desc";


	/**
	 * 查询Hive元数据中某库下某张表时间区间内的HDFS路径
	 */
	String HIVE_SQL_ORACLE_BEE =
			"SELECT \n" +
					"(\n" +
					"case\n" +
					"   when  p.PART_NAME is null then s.LOCATION\n" +
					"else\n" +
					"   CONCAT(CONCAT(s.LOCATION,'/'), p.PART_NAME)\n" +
					"end \n" +
					") location\n" +
					"FROM DBS d \n" +
					"INNER JOIN TBLS t \n" +
					"ON d.DB_ID = t.DB_ID  \n" +
					"LEFT JOIN SDS s \n" +
					"ON s.SD_ID = t.SD_ID\n" +
					"LEFT JOIN PARTITIONS p \n" +
					"ON p.TBL_ID = t.TBL_ID \n" +
					"LEFT JOIN PARTITION_KEYS pk \n" +
					"ON p.TBL_ID = pk.TBL_ID \n" +
					"LEFT JOIN PARTITION_KEY_VALS pkv \n" +
					"ON pkv.PART_ID = p.PART_ID \n" +
					"AND pk.INTEGER_IDX = pkv.INTEGER_IDX \n" +
					"WHERE d.NAME = ? " +
					"AND t.TBL_NAME = ? \n" +
					"AND pk.PKEY_NAME = ? \n" +
					"${sql} \n" +
					"${day} \n" +
					"ORDER BY pkv.PART_KEY_VAL desc";
	/**
	 * 查询时间区间内DDL库表信息
	 */
	String HIVE_SQL_ORACLE_LATEST_TABLE =
			"select \n" +
			"D.NAME dbName , \n" +
			"T.TBL_NAME tableName \n" +
			"FROM \n" +
			"(SELECT \n" +
			"T.DB_ID,\n" +
			"T.TBL_ID,\n" +
			"T.TBL_NAME\n" +
			"FROM TBLS T,\n" +
			"(SELECT * FROM TABLE_PARAMS TP3\n" +
			"WHERE ( tp3.PARAM_KEY  = 'transient_lastDdlTime' OR tp3.PARAM_KEY  = 'last_modified_time') AND TO_NCHAR(tp3.PARAM_VALUE)  BETWEEN  ? AND ?) TP3\n" +
			"where T.TBL_ID = TP3.TBL_ID \n" +
			"group by T.DB_ID,\n" +
			"T.TBL_ID,\n" +
			"T.TBL_NAME\n" +
			") T\n" +
			"INNER JOIN DBS D \n" +
			"ON T.DB_ID = D.DB_ID \n" +
			"AND D.NAME = ?";
	/**
	 * @Method
	 * @Param null
	 * @Return
	 * @Description 查询元数据库中的库表信息
	 * @Author Wei MinYe
	 * @Date 2024/3/27 11:02
	 * @Version V1.0
	 */
	String HIVE_SQL_ORACLE_TABLE_LIST_WHERE =
			"SELECT \n" +
			"D.NAME dbName, \n" +
			"T.TBL_NAME tableName, \n" +
			"CONCAT(CONCAT(D.NAME,'.'),T.TBL_NAME) dbTableName  \n" +
			" FROM TBLS T \n" +
			" JOIN DBS D \n" +
			" ON T.DB_ID = D.DB_ID \n" +
			"WHERE D.NAME NOT LIKE 'uat_%' " +
			"and D.NAME NOT IN ('default','ke','kylindb','mdm','xy','test','tests','tmp') " +
			"and T.TBL_TYPE not in ('VIRTUAL_VIEW') ";

	/**
	 * 查询元数据SQL(查询该库下的所有表信息)
	 */
	String HIVE_SQL_ORACLE_TABLE_LIST =
			"SELECT \n" +
			"t.TBL_NAME tblName,\n" +
			"s.LOCATION\n" +
			"FROM DBS d \n" +
			"INNER JOIN TBLS t \n" +
			"ON d.DB_ID = t.DB_ID  \n" +
			"LEFT JOIN SDS s \n" +
			"ON s.SD_ID = t.SD_ID\n" +
			"WHERE d.NAME = ? \n" +
			"AND t.TBL_TYPE not in ('VIRTUAL_VIEW') \n" +
			"${sql} \n";

	/**
	 * 全库同步
	 */
	String HIVE_SQL_ORACLE_COMPLETE =
			"SELECT \n" +
			"concat(concat(d.name,'.'),t.TBL_NAME) tblName \n" +
			"FROM DBS d \n" +
			"INNER JOIN TBLS t \n" +
			"ON d.DB_ID = t.DB_ID  \n" +
			"WHERE \n" +
			"d.NAME = ? \n" +
			"AND t.TBL_TYPE not in ('VIRTUAL_VIEW') ";

		}
